﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;
using System.IO;

namespace QLKS
{
    public class Mysql
    {
        MySqlConnection conn = null;
        public void ConnectToMysql() {
            if (!File.Exists(@"Setting.ini"))
            {
                // Chua co file - tao file nhap cau hinh mac dinh;
                FileStream Settingfile = File.Create(@"Setting.ini");
                StreamWriter wr = new StreamWriter(Settingfile);
                wr.WriteLine("172.245.220.253");
                wr.WriteLine("ffeefcom_mnhote");
                wr.WriteLine("ffeefcom_mnhote");
                wr.WriteLine("hungthai");
                wr.Close();
                Settingfile.Close();
            }
            // Da co file - doc file;
            FileStream Stf = File.OpenRead(@"Setting.ini");
            StreamReader sr = new StreamReader(Stf);
            string server = sr.ReadLine();
            string database = sr.ReadLine();
            string user = sr.ReadLine();
            string pass = sr.ReadLine();
            sr.Close();
            Stf.Close();
            //String str = @"Server=172.245.220.253;Database=ffeefcom_mnhote;Uid=ffeefcom_mnhote;Pwd=hungthai;charset=utf8;";
            //String str = @"Server=localhost;Database=mnhotel_datahotel;Uid=mnhotel;Pwd=hungthai;charset=utf8;";
            String str = @"Server="+server+";Database="+database+";Uid="+user+";Pwd="+pass+";charset=utf8;";
            try
            {
                this.conn = new MySqlConnection(str);
                this.conn.Open(); //open the connection
            }
            catch (MySqlException err) //We will capture and display any MySql errors that will occur
            {
                MessageBox.Show("Error: " + err.ToString(),"Thong Bao");
            }
            //return result connect
            
        }

        public DataSet SelectSqlCommand(string cmdText, MySqlConnection conn, Dictionary<string,string> Parameter)
        {
            MySqlCommand cmd = new MySqlCommand(cmdText, conn);
            cmd.Prepare();
            //we will bound a value to the placeholder
            cmd.Parameters.Add(Parameter);
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            //cmd.ExecuteNonQuery(); //execute the mysql command
            //return result
            return ds;
        }

        public MySqlCommand AddParams(MySqlCommand cmd, Dictionary<string, string> Params)
        {
            foreach (KeyValuePair<string, string> pr in Params)
            {
                cmd.Parameters.Add(new MySqlParameter(pr.Key, pr.Value));
            }
            return cmd;
        }

        public Dictionary<string, string> SelectReaderSqlCommand(string cmdText, Dictionary<string, string> Params = null)
        {
            MySqlDataReader reader = null;
            Dictionary<string, string> result = new Dictionary<string,string>();
            //result.Add("hung", "hung");
            try
            {
                this.ConnectToMysql();
                MySqlCommand cmd = new MySqlCommand(cmdText, this.conn);
                //add Params
                if (Params != null)
                    cmd = this.AddParams(cmd, Params);
                
                reader = cmd.ExecuteReader(); //execure the reader
                int col = 0;
                while (col < reader.FieldCount)
                {
                    reader.Read();
                    result[reader.GetName(col)] = reader.GetString(col);
                    col++;
                }
            }
            catch (MySqlException err)
            {
                Console.WriteLine("Error: " + err.ToString());
            }
            finally
            {
                if(reader!=null)
                    reader.Close();
                if(this.conn!=null)
                    this.CloseConnection();
            }
            return result;
        }

        public DataTable SelectReaderSqlCommandDatatable(string cmdText, Dictionary<string, string> Params = null)
        {
            MySqlDataReader reader = null;
            DataTable dt = null;
            //result.Add("hung", "hung");
            try
            {
                this.ConnectToMysql();
                MySqlCommand cmd = new MySqlCommand(cmdText, this.conn);
                //add Params
                if (Params != null)
                    cmd = this.AddParams(cmd, Params);

                reader = cmd.ExecuteReader(); //execure the reader
                dt = new DataTable();
                dt.Load(reader);
            }
            catch (MySqlException err)
            {
                Console.WriteLine("Error: " + err.ToString());
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                if (this.conn != null)
                    this.CloseConnection();
            }
            return dt;
        }

        public void NonQueryMysqlCommand(string cmdText, Dictionary<string,string> Params = null)
        {
            try
            {
                this.ConnectToMysql();
                MySqlCommand cmd = new MySqlCommand(cmdText, this.conn);
                //add Params
                if(Params != null)
                    cmd = this.AddParams(cmd, Params);

                cmd.ExecuteNonQuery(); //execure the reader
            }
            catch (MySqlException err)
            {
                Console.WriteLine("Error: " + err.ToString());
            }
            finally
            {
                if (this.conn != null)
                    this.CloseConnection();
            }
        }
        
        
       

        // Lấy danh sách phòng đang có khách;
        public DataTable ListRoomHaveUser()
        {
            string[] str = new string[11];
            string squery = "SELECT MaPNP as 'Số Hóa Đơn', TenLP as 'Loại Phòng',TenP as 'Phòng', TenKH as 'Khách', Diachi as 'Địa Chỉ', Dienthoai as 'Điện Thoại',CMND,Ngayvao as 'Ngày Vào',Ngayra as 'Ngày Ra',Thanhtien as 'Thành Tiền' FROM (SELECT MaP,ht_phong.MaLP,TenLP,TenP,ht_phong.Trangthai FROM ht_phong,ht_loaiphong WHERE (ht_phong.MaLP = ht_loaiphong.MaLP) AND (ht_phong.Trangthai=1)) as PLP, (SELECT ht_customers.MaKH,TenKH,Diachi,Dienthoai,CMND,MaPNP,MaP,Ngayvao,Ngayra,Thanhtien,Trangthai FROM ht_customers, ht_phieunp WHERE ht_customers.MaKH=ht_phieunp.MaKH) as KHP WHERE PLP.MaP = KHP.MaP";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable Trave = this.SelectReaderSqlCommandDatatable(squery, Params);
            return Trave;
        }
        //======================================================================#
        // Lấy số lượng phòng đang có;
        public int TotalRoom()
        {
            int totalroom = 0;
            string cmdText = "SELECT COUNT(DISTINCT MaP) FROM ht_phong";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAllRoom = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetAllRoom.Rows)
            {
                totalroom = Convert.ToInt32(row[0]);
            }
            return totalroom;
        }
        //========================================================================#
        //Lấy ID tất cả các phòng;
        public int GetIDRoom(int i)
        {
            int id = 0;
            string cmdText = "SELECT MaP FROM ht_phong";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAllRoom = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetAllRoom.Rows)
            {
                id = Convert.ToInt32(GetAllRoom.Rows[i][0]);
            }
            return id;
        }
        //========================================================================#
        //Lấy Name của phòng bằng ID
        public string GetNameRoomByID(string ID)
        {
            string cmdText = "SELECT TenP FROM ht_phong WHERE MaP=?map";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            Params["map"] = ID;
            DataTable GetIDRoomByName = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            string id = "";
            foreach (DataRow row in GetIDRoomByName.Rows)
            {
                id = row["TenP"].ToString();
            }
            return id;
        }
        //========================================================================#
        //Lấy ID của phòng bằng Name
        public string GetIDRoomByName(string TenP)
        {
            string cmdText = "SELECT MaP FROM ht_phong WHERE TenP=?tenp";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            Params["tenp"] = TenP;
            DataTable GetIDRoomByName = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            string id = "";
            foreach (DataRow row in GetIDRoomByName.Rows)
            {
                id = row["MaP"].ToString();
            }
            return id;
        }
        //=========================================================================#
        // Lấy trạng thái hoạt động của phòng - hoạt động = 0;
        public bool GetMRoom(int i)
        {
            bool status = false;
            string cmdText = "SELECT TrangThai FROM ht_phong WHERE MaP=" + i;
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAllRoom = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetAllRoom.Rows)
            {
                if (Convert.ToInt32(GetAllRoom.Rows[0][0]) == 1)
                    status = true;
            }
            return status;
        }
        //=========================================================================#
        // Phòng còn khách hay không; Cần kiểm tra lại;
        public bool GetStatusRoom(int i)
        {
            bool status = false;
            string cmdText = "SELECT TrangThai FROM ht_phieunp WHERE MaP=" + i +" AND Trangthai ='1'";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAllRoom = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetAllRoom.Rows)
            {
                if (Convert.ToInt32(GetAllRoom.Rows[0][0]) == 1) 
                    status = true;
            }
            return status;
        }
        //================================================================//
        // Lấy thông tin khách hàng;
        public string[] CustomersInfo(int i)
        {

            string[] customersinfo = new string[5];
            string cmdText = "SELECT * FROM ht_customers WHERE MaKH=" + i;
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAllRoom = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetAllRoom.Rows)
            {
                for (int j = 0; j < 5; j++)
                {
                    customersinfo[j] = GetAllRoom.Rows[0][j].ToString();

                }

            }
            return customersinfo;
        }
        //==================================================================//
        // Lấy thông tin phòng cần thanh toán;
        public DataTable GetDataRoomID(int i)
        {
            string cmdText = "SELECT * FROM (SELECT MLP.MaP,MLP.MaLP,MKH.MaPNP,MLP.TenLP,MLP.TenP,MKH.TenKH,MKH.CMND,MLP.Giagio,MLP.Giangay,MKH.Ngayvao,MKH.Ngayra FROM (SELECT ht_phieunp.MaP,ht_phieunp.MaPNP,ht_phieunp.Ngayvao,ht_phieunp.Ngayra,ht_customers.TenKH,ht_customers.CMND FROM ht_phieunp,ht_customers WHERE ht_phieunp.MaKH = ht_customers.MaKH) MKH, (SELECT ht_phong.MaP,ht_phong.MaLP,ht_loaiphong.Giagio,ht_loaiphong.Giangay,ht_loaiphong.TenLP, ht_phong.TenP FROM ht_phong,ht_loaiphong WHERE ht_phong.MaLP = ht_loaiphong.MaLP) MLP WHERE MKH.MaP = MLP.MaP) GET WHERE GET.MaP =" + i;
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAlInfo = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            return GetAlInfo;
        }

        //===================================================================#
        // Lấy trạng thái VIP;
        public bool GetVIPRoom(int i)
        {
            bool status = false;
            string cmdText = "SELECT MaLP FROM ht_phong WHERE MaP=" + i;
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetVR = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetVR.Rows)
            {
                if (Convert.ToInt32(GetVR.Rows[0][0]) == 2)
                    status = true;
            }
            return status;
        }
        //===================================================================#
        // Lấy số lượng phòng có thể sử dụng;
        public int GetClrNumRoom()
        {
            int sophong = 0;
            string cmdText = "SELECT COUNT(DISTINCT MaP) FROM ht_phong WHERE ht_phong.Trangthai =1";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAllRoom = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetAllRoom.Rows)
            {
                sophong = Convert.ToInt32(row[0]);
            }
            return sophong;
        }
        //===================================================================#
        // Kiểm tra khách có đang thuê phòng hay không ?
        public int GetNumCustomerRoom(string MaKH)
        {
            int num = 0;
            string cmdText = "SELECT COUNT(DISTINCT MaKH) as 'So luong' FROM ht_phieunp WHERE ht_phieunp.Trangthai ='1' AND MaKH=?makh";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            Params["makh"] = MaKH;
            DataTable CountKH = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in CountKH.Rows)
            {
                num = Convert.ToInt32(row["So luong"]);
            }
            return num;
        }
        //====================================================================#
        // Lấy số lượng phòng chưa thanh toán;
        public int GetNPayNumRoom()
        {
            int sophong = 0;
            string cmdText = "SELECT COUNT(DISTINCT MaP) FROM ht_phieunp WHERE ht_phieunp.Trangthai =2";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAllRoom = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetAllRoom.Rows)
            {
                sophong = Convert.ToInt32(row[0]);
            }
            return sophong;
        }
        //====================================================================#
        //Tên phòng có thể cho thuê;
        public string[] GetAllNameRoomClr(int i)
        {
            int n = i;
            string[] name = new string[n];
            string cmdText = "SELECT ht_phong.MaP, ht_loaiphong.TenLP, ht_phong.TenP,ht_phong.Trangthai From ht_phong, ht_loaiphong WHERE ht_phong.MaLP=ht_loaiphong.MaLP AND ht_phong.Trangthai=1";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAllRoom = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetAllRoom.Rows)
            {
                for (int j = 0; j < i; j++)
                {
                    name[j] = GetAllRoom.Rows[j][1].ToString() + " " + GetAllRoom.Rows[j][2].ToString();

                }

            }
            return name;
        }
        // Lay Ten Phong;
        public string GetNameRoom(int i)
        {
            string name = "";
            string cmdText = "SELECT ht_phong.MaP, ht_loaiphong.TenLP, ht_phong.TenP From ht_phong, ht_loaiphong WHERE ht_phong.MaLP=ht_loaiphong.MaLP";
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable GetAllRoom = this.SelectReaderSqlCommandDatatable(cmdText, Params);
            foreach (DataRow row in GetAllRoom.Rows)
            {
                name = GetAllRoom.Rows[i][1].ToString() + " " + GetAllRoom.Rows[i][2].ToString();
            }
            return name;
        }
        // END;
        public void CloseConnection()
        {
            this.conn.Close();
        }
    }
}
